package com.bert.thymeleafgenerator;

import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;

class SQL_Generate {
    // 4分位统计自动生成SQL代码
    public static void main(String[] args) {
        // 修改文件保存路径再运行
//        String root = "C:\\Users\\Administrator\\Desktop\\txt\\SQL_4\\";
        String root = "src/main/resources/SQL_Auto_File/";

        String[] st = { "水分%","脂肪%","蛋白质%","粗纤维%","容重g/L",
                        "千粒重g/1000","草籽","昆虫","霉变粒","病斑",
                        "虫蚀","种衣剂","热损粒","损伤粒(粒/kg)","碳化粒(粒/kg)",
                        "未熟粒(粒/kg)","破碎粒(粒/kg)","黑尖","发芽","不完善",
                        "样重","视觉称重","筛上杂质","筛下杂质","中筛不完整",
                        "检出样重","遗存撒落样重","遗存撒落%","大型杂质","小型杂质",
                        "有机杂质","大样杂质","检疫特征粒(g)","检验特征粒(g)","小样杂质",
                        "完整完好粒" };
        StringBuilder fw2 = new StringBuilder();
        StringBuilder fw3 = new StringBuilder();
        StringBuilder fw_mid = new StringBuilder();
        StringBuilder minmax_1 = new StringBuilder();
        StringBuilder minmax = new StringBuilder();
        StringBuilder yxz_mid = new StringBuilder();

        fw2.append("SELECT\n" +
                "\t`v_bb_kj_cstj_4fw_1`.`粮种` AS `粮种`,\n" +
                "\t`v_bb_kj_cstj_4fw_1`.`日期` AS `日期`,\n" +
                "\t`v_bb_kj_cstj_4fw_1`.`Q1r` AS `Q1r`,\n" +
                "\t`v_bb_kj_cstj_4fw_1`.`Q2r` AS `Q2r`,\n" +
                "\t`v_bb_kj_cstj_4fw_1`.`Q3r` AS `Q3r`,\n");

        fw3.append("SELECT\n" +
                "\t`v_bb_kj_cstj_4fw_2`.`粮种` AS `粮种`,\n" +
                "\t`v_bb_kj_cstj_4fw_2`.`日期` AS `日期`,\n");

        fw_mid.append("SELECT\n" +
                "\t`v_bb_kj_cstj_4fw_minmax`.`粮种` AS `粮种`,\n" +
                "\t`v_bb_kj_cstj_4fw_minmax`.`日期` AS `日期`,\n" +
                "\t'上限' AS `批次号`,\n" +
                "\t'' AS `批次id`,\n" +
                "\t'' AS `有效性`,\n");

        minmax_1.append("SELECT\n" +
                "\t`v_bb_kj_cstj_4fw_3`.`粮种` AS `粮种`,\n" +
                "\t`v_bb_kj_cstj_4fw_3`.`日期` AS `日期`,\n");

        minmax.append("SELECT\n" +
                "\t`v_bb_kj_cstj_4fw_minmax_1`.`粮种` AS `粮种`,\n" +
                "\t`v_bb_kj_cstj_4fw_minmax_1`.`日期` AS `日期`,\n");

        yxz_mid.append("SELECT\n" +
                "\t`v_bb_kj_cstj_pjz`.`粮种` AS `粮种`,\n" +
                "\t`v_bb_kj_cstj_pjz`.`日期` AS `日期`,\n" +
                "\t'智能检测有效值' AS `批次号`,\n" +
                "\t'' AS `批次id`,\n");

        for (int i = 0; i < st.length; i++) {

            fw2.append("\tmax(CASE WHEN (`v_bb_kj_cstj_4fw_1`.`"+ st[i] + "id` = floor(`v_bb_kj_cstj_4fw_1`.`Q1r`)) THEN `v_bb_kj_cstj_4fw_1`.`"+ st[i] + "` ELSE NULL END) AS `"+ st[i] + "1a`,\n" +
                    "\tmax(CASE WHEN (`v_bb_kj_cstj_4fw_1`.`"+ st[i] + "id` = floor(`v_bb_kj_cstj_4fw_1`.`Q1r`) + 1) THEN `v_bb_kj_cstj_4fw_1`.`"+ st[i] + "` ELSE NULL END) AS `"+ st[i] + "1b`,\n" +
                    "\tmax(`v_bb_kj_cstj_4fw_1`.`Q1r` - floor(`v_bb_kj_cstj_4fw_1`.`Q1r`)) AS `"+ st[i] + "1c`,\n" +
                    "\tmax(CASE WHEN (`v_bb_kj_cstj_4fw_1`.`"+ st[i] + "id` = floor(`v_bb_kj_cstj_4fw_1`.`Q2r`)) THEN `v_bb_kj_cstj_4fw_1`.`"+ st[i] + "` ELSE NULL END) AS `"+ st[i] + "2a`,\n" +
                    "\tmax(CASE WHEN (`v_bb_kj_cstj_4fw_1`.`"+ st[i] + "id` = floor(`v_bb_kj_cstj_4fw_1`.`Q2r`) + 1) THEN `v_bb_kj_cstj_4fw_1`.`"+ st[i] + "` ELSE NULL END) AS `"+ st[i] + "2b`,\n" +
                    "\tmax(`v_bb_kj_cstj_4fw_1`.`Q2r` - floor(`v_bb_kj_cstj_4fw_1`.`Q2r`)) AS `"+ st[i] + "2c`,\n" +
                    "\tmax(CASE WHEN (`v_bb_kj_cstj_4fw_1`.`"+ st[i] + "id` = floor(`v_bb_kj_cstj_4fw_1`.`Q3r`)) THEN `v_bb_kj_cstj_4fw_1`.`"+ st[i] + "` ELSE NULL END) AS `"+ st[i] + "3a`,\n" +
                    "\tmax(CASE WHEN (`v_bb_kj_cstj_4fw_1`.`"+ st[i] + "id` = floor(`v_bb_kj_cstj_4fw_1`.`Q3r`) + 1) THEN `v_bb_kj_cstj_4fw_1`.`"+ st[i] + "` ELSE NULL  END) AS `"+ st[i] +"3b`,\n" +
                    "\tmax(`v_bb_kj_cstj_4fw_1`.`Q3r` - floor(`v_bb_kj_cstj_4fw_1`.`Q3r`)) AS `"+ st[i] + "3c`,\n");

            fw3.append("\t(`v_bb_kj_cstj_4fw_2`.`"+ st[i] +"1a` + ((`v_bb_kj_cstj_4fw_2`.`"+ st[i] +"1b` - `v_bb_kj_cstj_4fw_2`.`"+ st[i] +"1a`) * `v_bb_kj_cstj_4fw_2`.`"+ st[i] +"1c`)) AS `"+ st[i] +"Q1`,\n" +
                    "\t(`v_bb_kj_cstj_4fw_2`.`"+ st[i] +"2a` + ((`v_bb_kj_cstj_4fw_2`.`"+ st[i] +"2b` - `v_bb_kj_cstj_4fw_2`.`"+ st[i] +"2a`) * `v_bb_kj_cstj_4fw_2`.`"+ st[i] +"2c`)) AS `"+ st[i] +"Q2`,\n" +
                    "\t(`v_bb_kj_cstj_4fw_2`.`"+ st[i] +"3a` + ((`v_bb_kj_cstj_4fw_2`.`"+ st[i] +"3b` - `v_bb_kj_cstj_4fw_2`.`"+ st[i] +"3a`) * `v_bb_kj_cstj_4fw_2`.`"+ st[i] +"3c`)) AS `"+ st[i] +"Q3`,\n");

            fw_mid.append("\tround( `v_bb_kj_cstj_4fw_minmax`.`"+ st[i] +"mid`, 2 ) AS `"+ st[i] +"mid`,\n");

            minmax_1.append("\t(`v_bb_kj_cstj_4fw_3`.`"+ st[i] +"Q1` - (`bs_tjcs`.`bs` * (`v_bb_kj_cstj_4fw_3`.`"+ st[i] +"Q3` - `v_bb_kj_cstj_4fw_3`.`"+ st[i] +"Q1`))) AS `"+ st[i] +"min`,\n" +
                    "\t(`v_bb_kj_cstj_4fw_3`.`"+ st[i] +"Q2` + (`bs_tjcs`.`bs` * (`v_bb_kj_cstj_4fw_3`.`"+ st[i] +"Q2` - `v_bb_kj_cstj_4fw_3`.`"+ st[i] +"Q2`))) AS `"+ st[i] +"mid`,\n" +
                    "\t(`v_bb_kj_cstj_4fw_3`.`"+ st[i] +"Q3` + (`bs_tjcs`.`bs` * (`v_bb_kj_cstj_4fw_3`.`"+ st[i] +"Q3` - `v_bb_kj_cstj_4fw_3`.`"+ st[i] +"Q1`))) AS `"+ st[i] +"max`,\n");

            minmax.append("\t(`v_bb_kj_cstj_4fw_minmax_1`.`"+ st[i] +"min` / ( 1 + `bs_tjcs`.`jcx` )) AS `"+ st[i] +"min`,\n" +
                    "\t(`v_bb_kj_cstj_4fw_minmax_1`.`"+ st[i] +"mid` * ( 1 + `bs_tjcs`.`jcx` )) AS `"+ st[i] +"mid`,\n" +
                    "\t(`v_bb_kj_cstj_4fw_minmax_1`.`"+ st[i] +"max` * ( 1 + `bs_tjcs`.`jcx` )) AS `"+ st[i] +"max`,\n");

            yxz_mid.append("\tround((`v_bb_kj_cstj_pjz`.`"+ st[i] +"`), 2) AS `"+ st[i] +"`,\n");
        }
        fw2.append("\tmin(`v_bb_kj_cstj_4fw_1`.`开始`) AS `开始`,\n\tmax(`v_bb_kj_cstj_4fw_1`.`结束`) AS `结束`\nFROM\n\t`v_bb_kj_cstj_4fw_1` GROUP BY `v_bb_kj_cstj_4fw_1`.`日期`");
        fw3.append("\t`v_bb_kj_cstj_4fw_2`.`开始` AS `开始`,\n\t`v_bb_kj_cstj_4fw_2`.`结束` AS `结束`\nFROM\n\t`v_bb_kj_cstj_4fw_2`");
        fw_mid.append("\t'' AS `杂质含量`,\n" +
                "\t'' AS `大样杂质含量%`,\n" +
                "\t'' AS `小样杂质含量%`,\n" +
                "\t'' AS `不完整粒含量%`,\n" +
                "\t'车批' AS `批次归类`,\n" +
                "\t'' AS `单次取样量（g)`,\n" +
                "\t'' AS `取样间隔(s)`,\n" +
                "\t'' AS `混样量(次)`,\n" +
                "\t`v_bb_kj_cstj_4fw_minmax`.`开始` AS `开始`,\n" +
                "\t`v_bb_kj_cstj_4fw_minmax`.`结束` AS `结束` \n" +
                "FROM\n" +
                "\t`v_bb_kj_cstj_4fw_minmax`");
        minmax_1.append("\t`v_bb_kj_cstj_4fw_3`.`开始` AS `开始`,\n\t`v_bb_kj_cstj_4fw_3`.`结束` AS `结束`\nFROM\n\t(`v_bb_kj_cstj_4fw_3` join `bs_tjcs`)");
        minmax.append("\t`v_bb_kj_cstj_4fw_minmax_1`.`开始` AS `开始`,\n\t`v_bb_kj_cstj_4fw_minmax_1`.`结束` AS `结束`\nFROM\n\t( `v_bb_kj_cstj_4fw_minmax_1` JOIN `bs_tjcs`)");
        yxz_mid.append("\t'' AS `杂质含量`,\n" +
                "\t'' AS `大样杂质含量%`,\n" +
                "\t'' AS `小样杂质含量%`,\n" +
                "\t'' AS `不完整粒含量%`,\n" +
                "\t'' AS `批次归类`,\n" +
                "\t'' AS `单次取样量（g)`,\n" +
                "\t'' AS `取样间隔(s)`,\n" +
                "\t'' AS `混样量(次)`,\n" +
                "\t`v_bb_kj_cstj_pjz`.`开始` AS `开始`,\n" +
                "\t`v_bb_kj_cstj_pjz`.`结束` AS `结束` \n" +
                "FROM\n" +
                "\t(`v_bb_kj_cstj_pjz` JOIN `bs_tjcs`)");
        String[] filePath = {   root + "v_bb_kj_cstj_4fw_2.txt",
                                root + "v_bb_kj_cstj_4fw_3.txt",
                                root + "v_bb_kj_cstj_4fw_mid.txt",
                                root + "v_bb_kj_cstj_4fw_minmax_1.txt",
                                root + "v_bb_kj_cstj_4fw_minmax.txt",
                                root + "v_bb_kj_cstj_yxz_mid.txt" };
        for (int i = 0; i < filePath.length; i++) {
            File file = new File(filePath[i]);
            if (!file.getParentFile().exists()) {
                file.getParentFile().mkdirs();
            }
        }
        try {
            Files.writeString(Paths.get(filePath[0]), fw2);
            Files.writeString(Paths.get(filePath[1]), fw3);
            Files.writeString(Paths.get(filePath[2]), fw_mid);
            Files.writeString(Paths.get(filePath[3]), minmax_1);
            Files.writeString(Paths.get(filePath[4]), minmax);
            Files.writeString(Paths.get(filePath[5]), yxz_mid);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


}